use master
go 
create database bbs
on(
	name='bbs',
	filename='D:\11\bbs.mdf',
	size=5,
	maxsize=100,
	filegrowth=10%
)
log on(
	name='bbs_log',
	filename='D:\11\bbs_log.mdf',
	size=5,
	maxsize=100,
	filegrowth=10%
)
go
use bbs
go
create table bbsUsers
(
	uID int identity(1,1) not null ,
	uName nvarchar(10) not null,
	uSex  varchar(2) not null,
	uAge  int not null,
	uPoint  int not null

)
alter table bbsUsers add constraint PK_bbsUsers_uID primary key(uID)
alter table bbsUsers add constraint UK_bbsUsers_uName unique(uName)
alter table bbsUsers add constraint CK_bbsUsers_uSex check(uSex='男' or uSex='女')
alter table bbsUsers add constraint CK_bbsUsers_uAge check(uAge>=15 or uAge<=60)
alter table bbsUsers add constraint CK_bbsUsers_uPoint check(uPoint>=0)
create table bbsSection
(
	sID  int  identity(1,1) not null ,
	sName  varchar(10) not null,
	sUid   int ,

)
alter table bbsSection add constraint PK_bbsSection_sID primary key(sID)
alter table bbsSection add constraint FK_bbsSection_sUid foreign key(sUid)references bbsUsers(uID)
create table bbsTopic
(
	tID  int primary key identity(1,1) not null,
	tUID  int references bbsUsers(uID) not null  ,
    tSID int references  bbsSection(sID) ,
	tTitle  varchar(100) not null,
	tMsg  text  not null,
	tTime  datetime  not null,
	tCount  int not null,
)
create table bbsReply
(
	rID  int primary key identity(1,1) not null,
	rUID int references bbsUsers(uID) ,
	rTID  int references bbsTopic(tID) ,
	rMsg  text  not null,
	rTime  datetime ,
)
go
use bbs
go
select * from bbsSection
select * from bbsReply
select * from bbsTopic
select * from bbsUsers
insert into bbsUsers values('小雨点','女',20,0),('逍遥','男',18,4),('七年级生','男',19,2)
insert into bbsSection values('技术名流',1),('读书世界',3),('生活百科',1),('八卦区',3)
insert into bbsTopic values(2,4,'范跑跑','谁是范跑跑', 2008-7-8,1),(3,2,'.NET','与JAVA的区别是什么呀？',2008-9-1,2),(1,4,'今年夏天最流行什么','有谁知道今年夏天最流行什么呀？',2008-9-10,0)
insert into bbsReply values(2,2,'一名地震自己先跑的教师',2008-7-8),(3,3,'不知道',2008-9-1),(1,1,'流行穿黑裙子',2008-9-10)
update  bbsUsers set  uPoint=30 where uName='小雨点'
--在论坛数据库中完成以下题目
--1.在主贴表中统计每个版块的发帖总数
select tSID, count(*) from bbsTopic group by tSID
--2.在回帖表中统计每个主贴的回帖总数量
select rTID, count(*) from bbsReply group by rTID
--3.在主贴表中统计每个用户的发的主帖的总数
select tUID, count(*) from bbsTopic group by tUID
--4.在主贴表中统计每个用户发的主贴的回复数量总和
select tCount,tUID, count(*) from bbsTopic group by tCount,tUID order by tUID
--5.在主贴表中查询每个版块的主贴的平均回复数量大于3的版块的平均回复数量
select tCount,tSID, count(*),AVG(tCount) from bbsTopic group by tCount,tSID having AVG(tCount)>3
--6.在用户信息表中查询出积分最高的用户的用户名，性别，年龄和积分
select TOP 1 * from bbsUsers order by uPoint DESC
--7.在主贴表中（bbsTopic）中将帖子的内容或标题中有“快乐”两字的记录查询出来
select * from bbsTopic where tTitle like '%快乐%' or tMsg like '%快乐%'
--8.在用户信息表（bbsUsers）中将用户年龄在15-20之间并且积分在10分以上的优秀用户查询出来（用多种方法实现）
select * from bbsUsers where uAge>=15 and uAge<=20 and uPoint>10
select top 1 * from bbsUsers where uAge>=15 and uAge<=20 order by uPoint DESC
--9.在用户信息表（bbsUsers）中将用户名的第一个字为“小”，第三字为“大”的用户信息查询出来
select * from bbsUsers where uName like '小_大%'
--10.在主贴表（bbsTopic）中将在2008-9-10 12:00:00 以后发的并且回复数量在10以上的帖子的标题和内容查询出来，并且为列取上对应的中文列名
select tTitle 标题,tMsg 内容 from bbsTopic where  tTime between '2008-9-10 12:00:00' and getdate() and tCount>10
--11.在主贴表（bbsTopic）中将帖子的标题是以‘！’结尾的帖子的发帖人编号和回复数量查询出来
select tUID,tCount from bbsTopic where tTitle like '%!'

--先创建如图所示表

--订单表（orders）列为：订单编号（orderId 主键）  订购日期（orderDate）

--订购项目表（orderItem），列为：
--项目编号（ItemiD）订单编号（orderId）产品类别（itemType）
--产品名称（itemName） 订购数量（theNumber）  订购单价（theMoney）
use bbs
go
create table orders
(
	orderId int primary key identity,
	orderDate datetime,
)

create table orderItem
(
	ItemiD int primary key identity,
	orderId int references orders(orderId),
	itemType nvarchar(20),
	itemName nvarchar(20),
	theNumber int,
	theMoney money
)
select * from orders
select * from orderItem
insert into orders values('2008-01-12 00:00:00.000'),
('2008-02-10 00:00:00.000'),
('2008-02-15 00:00:00.000'),
('2008-03-10 00:00:00.000')
insert into orderItem values(1,'文具','笔',72,'2'),
(1,'文具','尺',10,'1'),
(1,'体育用品','篮球',1,'56'),
(2,'文具','笔',36,'2'),
(2,'文具','固体胶',20,'3'),
(2,'日常用品','透明胶',2,'1'),
(2,'体育用品','羽毛球',20,'3'),
(3,'文具','订书机',20,'3'),
(3,'文具','订书针',20,'3'),
(3,'文具','裁纸刀',5,'5'),
(4,'文具','笔',20,'2'),
(4,'文具','信封',50,'1'),
(4,'日常用品','毛巾',4,'5'),
(4,'日常用品','透明胶',30,'1'),
(4,'体育用品','羽毛球',20,'3')
--1.查询所有订单订购的所有物品数量总和
select SUM(theNumber) from orderItem
--2.查询订单编号小于3的，平均单价小于10 每个订单订购的所有物品的数量和以及平均单价
select theNumber 物品的数量,orderId 订单编号,AVG(theMoney)平均单价 from orderltem group by theNumber,orderId having orderId<3 and AVG(theMoney)<10
--3.查询平均单价小于10并且总数量大于 50 每个订单订购的所有物品数量和以及平均单价
select theNumber 物品数量,AVG(theMoney) 平均单价 from orderltem group by theNumber having AVG(theMoney)<10 and theNumber>50
--4.查询每种类别的产品分别订购了几次，例如：
--					文具      9
--                    体育用品  3
--                    日常用品  3
select itemType, count(*) from orderItem group by itemType
--5.查询每种类别的产品的订购总数量在100以上的订购总数量和平均单价
select itemType,sum(theNumber) 订购数量,AVG(theMoney) 平均单价 from orderItem group by itemType having sum(theNumber)>100
--6.查询每种产品的订购次数，订购总数量和订购的平均单价，例如：

--  产品名称   订购次数  总数量   平均单价 
--    笔           3       120       2

select itemType 产品名称,count(itemType) 订购次数,sum(theNumber) 总数量,AVG(theMoney) 平均单价 from orderItem group by itemType